﻿using CNative.DbUtils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CNative.EntityBuilder.DAL
{
    public class DBMetadata
    {
        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbName">构造参数，可以为数据库连接字符串</param>
        public DBMetadata(string dbName)
        {
            DbName = dbName;
            _db = new DbHelper(dbName);
        } 
        #endregion
        protected string DbName { get; set; }
        /// <summary>
        /// 连接上下文IDbHelper
        /// </summary>
        protected DbHelper _db { get; set; }
        /// <summary>
        /// 数据库类型
        /// </summary>
        public DatabaseType  dbType { get { return _db.DBType; } }

        static Dictionary<string, List<DbColumnInfo>> _DicDbTableInfo = new Dictionary<string, List<DbColumnInfo>>();
        /// <summary>
        /// 得到数据库对象
        /// </summary>
        List<string> GetObjects(SqlEntity sql,string colName= "name", string colName2 = "")
        {
            DataSet ds = _db.QueryDataSet(sql);

            var objs = new List<string>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (colName2.IsNullOrEmpty())
                        objs.Add(ds.Tables[0].Rows[i][colName].ToString());
                    else
                        objs.Add(ds.Tables[0].Rows[i][colName].ToString() + (ds.Tables[0].Rows[i][colName2] != null&& ds.Tables[0].Rows[i][colName2] != DBNull.Value ? "(" + ds.Tables[0].Rows[i][colName2].ToString() + ")" : ""));
                }
            }
            return objs;
        }

        /// <summary>
        /// 得到所有数据库
        /// </summary>
        public List<string> GetAllDatabases()
        {
            var sql = _db.CreateSqlEntity();
            if (dbType == DatabaseType.SqlServer)
            {
                sql.Sql = "SELECT Name FROM Master..SysDatabases ORDER BY Name";
            }
            else if (dbType == DatabaseType.Oracle)
            {
                #region Oracle
                sql.Sql = @"SELECT t.username AS ""name"",t.user_id FROM ALL_USERS T";
                #endregion
            }
            return GetObjects(sql);
        }

        /// <summary>
        /// 得到数据库中的所有表
        /// </summary>
        public List<string> GetAllTables(string dbName)
        {
            var sql = _db.CreateSqlEntity();
            if (dbType == DatabaseType.SqlServer)
            {
                sql.Sql = "SELECT name,xtype,(SELECT top 1 value FROM " + dbName + ".sys.extended_properties  WHERE " + dbName + ".sys.extended_properties.major_id = " + dbName + ".dbo.sysobjects.id  AND " + dbName + ".sys.extended_properties.minor_id=0) AS [Description] From " + dbName + ".dbo.sysobjects WHERE xtype = 'u'  ORDER BY name";
            }
            else if (dbType == DatabaseType.Oracle)
            {
                #region Oracle
                if (dbName.IsNullOrEmpty())
                    dbName = "SYS";
                dbName = dbName.ToUpper();
                sql.Sql = @"SELECT OWNER,TABLE_NAME AS ""Name"", COMMENTS AS ""Description""
                          FROM ALL_TAB_COMMENTS
                         WHERE TABLE_TYPE= 'TABLE' AND OWNER = '" + dbName + "'";
                #endregion
            }
            return GetObjects(sql, "name", "Description");
        }

        /// <summary>
        /// 得到数据库中的所有表
        /// </summary>
        public List<string> GetAllViews(string dbName)
        {
            var sql = _db.CreateSqlEntity();
            if (dbType == DatabaseType.SqlServer)
            {
                sql.Sql = "SELECT name,xtype,(SELECT top 1 value FROM " + dbName + ".sys.extended_properties  WHERE " + dbName + ".sys.extended_properties.major_id = " + dbName + ".dbo.sysobjects.id  AND " + dbName + ".sys.extended_properties.minor_id=0) AS [Description]  From " + dbName + ".dbo.sysobjects WHERE xtype='v' ORDER BY name";
            }
            else if (dbType == DatabaseType.Oracle)
            {
                #region Oracle
                if (dbName.IsNullOrEmpty())
                    dbName = "SYS";
                dbName = dbName.ToUpper();
                sql.Sql = @"SELECT OWNER,TABLE_NAME AS ""Name"", COMMENTS AS ""Description""
                          FROM ALL_TAB_COMMENTS
                         WHERE TABLE_TYPE= 'VIEW' AND OWNER = '" + dbName + "'";
                #endregion
            }
            return GetObjects(sql, "name", "Description");
        }

        /// <summary>
        /// 得到一个表的所有列信息
        /// </summary>
        public  List<DbColumnInfo> GetAllColumns(string tableName,string DbName)
        {
            if (tableName.IsNullOrEmpty())
                return new List<DbColumnInfo>();

            if (_DicDbTableInfo.ContainsKey(DbName + tableName))
            {
                return _DicDbTableInfo[DbName + tableName];
            }
            //---------------------------------------------------------------------
            var sqle = _db.CreateSqlEntity();
            if (dbType == DatabaseType.SqlServer)
            {
                #region SqlServer
                sqle.Sql = @"SELECT c.column_id AS [ColumnId], c.name AS [Name], t.name AS [Type], c.max_length AS [MaxLength],
                                    c.is_nullable [IsNullable],
                                    [IsIdentity] = CONVERT(BIT, ( SELECT    COUNT(*)
                                                                  FROM      " + DbName + @".sys.identity_columns
                                                                  WHERE     " + DbName + @".sys.identity_columns.object_id = c.object_id
                                                                            AND c.column_id = " + DbName + @".sys.identity_columns.column_id )),
                                    ( SELECT top 1   value
                                      FROM      " + DbName + @".sys.extended_properties
                                      WHERE     " + DbName + @".sys.extended_properties.major_id = c.object_id
                                                AND " + DbName + @".sys.extended_properties.minor_id = c.column_id
                                                /*AND name = 'MS_Description'*/ ) AS [Description],
                                    [IsPrimaryKey] = CONVERT(BIT, ( CASE WHEN c.name IN (
                                                                              SELECT    b.COLUMN_NAME
                                                                              FROM      " + DbName + @".INFORMATION_SCHEMA.TABLE_CONSTRAINTS a
                                                                              INNER JOIN " + DbName + @".INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
                                                                              WHERE     a.CONSTRAINT_TYPE = 'PRIMARY KEY'
                                                                                        AND a.TABLE_NAME = @table_name ) THEN 1
                                                                         ELSE 0
                                                                    END ))
                             FROM   " + DbName + @".sys.columns c,
                                    ( SELECT    object_id, name
                                      FROM      " + DbName + @".sys.tables
                                      UNION ALL
                                      SELECT    object_id, name
                                      FROM      " + DbName + @".sys.views ) b,
                                    " + DbName + @".sys.types t
                             WHERE  c.object_id = b.object_id
                                    AND ｃ.user_type_id = ｔ.user_type_id
                                    AND ｂ.name = @table_name
                                    AND ｔ.name != 'sysname'
                             ORDER BY ｃ.column_id ASC";
                #endregion
                sqle.Parameters = (new List<IDataParameter> { new SqlParameter("@table_name", tableName) });
            }
            else if (dbType == DatabaseType.Oracle)
            {
                #region Oracle
                sqle.Sql = @"SELECT A.COLUMN_ID AS ""ColumnId"", A.COLUMN_NAME AS ""Name"",
                               A.DATA_TYPE AS ""Type"", A.DATA_LENGTH AS ""MaxLength"",
                               CASE
                                 WHEN A.NULLABLE = 'N' THEN
                                  0
                                 ELSE
                                  1
                               END AS ""IsNullable"", B.COMMENTS AS ""Description"",
                               CASE
                                 WHEN (SELECT COUNT(*)
                                         FROM ALL_CONS_COLUMNS C
                                        WHERE C.TABLE_NAME = A.TABLE_NAME
                                          AND C.COLUMN_NAME = A.COLUMN_NAME
                                          AND C.CONSTRAINT_NAME =
                                              (SELECT D.CONSTRAINT_NAME
                                                 FROM ALL_CONSTRAINTS D
                                                WHERE D.TABLE_NAME = C.TABLE_NAME AND ROWNUM<2
                                                  AND D.CONSTRAINT_TYPE = 'P')) > 0 THEN
                                  1
                                 ELSE
                                  0
                               END AS ""IsPrimaryKey""

                          FROM ALL_TAB_COLS A, ALL_COL_COMMENTS B
                         WHERE A.TABLE_NAME = B.TABLE_NAME
                           AND B.COLUMN_NAME = A.COLUMN_NAME 
                           AND A.OWNER=B.OWNER AND A.OWNER='" + DbName + @"'
                           AND A.TABLE_NAME = '" + tableName + @"'
                         ORDER BY A.TABLE_NAME, A.COLUMN_ID";
                #endregion
            }
            var list = _db.Query<DbColumnInfo>(sqle);

            if (list != null && list.Count > 0)
                _DicDbTableInfo[DbName + tableName] = list;

            return list;
        }

        /// <summary>
        /// 得到指定表的主键列信息
        /// </summary>
        public List<DbColumnInfo> GetAllPrimaryKeys(string tableName, string DbName)
        {
            var cols = GetAllColumns(tableName, DbName);
            if (cols != null && cols.Count > 0)
                return cols.FindAll(f => f.IsPrimaryKey);

            return new List<DbColumnInfo>();
        }
        /// <summary>
        /// 得到指定表的非主键列信息
        /// </summary>
        public  List<DbColumnInfo> GetAllNotPrimaryKeys(string tableName, string DbName)
        {
            var cols = GetAllColumns(tableName, DbName);
            if (cols != null && cols.Count > 0)
                return cols.FindAll(f => !f.IsPrimaryKey);

            return new List<DbColumnInfo>();
        }

        /// <summary>
        /// 判断指定表是否存在主键
        /// </summary>
        public  bool IsHavePrimaryKey(string tableName, string DbName)
        {
            return GetAllColumns(tableName, DbName).Exists(f => f.IsPrimaryKey);
        }

    }
}
